<?php

namespace App\Http\Controllers\Home;

use App\Http\Controllers\Controller;
use App\Util\Result;
use Illuminate\Support\Facades\DB;

class BookController extends Controller
{
    //首页
    public function index() {
        DB::connection('sqlsrv2')->table('SysTempTab')->where("stt_id", 1)->update(['stt_value' => '2,5454564564']);
        $res = DB::table('wxxxb')
            ->select("正题名 as name",'责任者 as write','出版者 as public','索取号 as getCode'
            ,'页卷数 as pages')
            ->orderby("标识列","desc")
            ->limit(10)
            ->get();
        return view('home.index')->with('arr',$res);
    }

    //图书馆信息
    public function library() {
        $arr['all'] = DB::table('tmxxb')->count();
        $arr['borrow'] = DB::table('ltxxb')->count();
        $arr['back'] = DB::table('ltlsb')->count();
        return view('home.library')->with('arr',$arr);
    }

    //扫码界面
    public function barCode() {
        DB::connection('sqlsrv2')->table('SysTempTab')->where("stt_id", 1)->update(['stt_value' => '2,225556466565']);
        return view('home.barCode');
    }

    public function interval() {
        $sel = DB::connection('sqlsrv2')->select("select top 1 stt_id,stt_value from SysTempTab Order by stt_id desc");
        if(!empty($sel)) {
            $item = explode(",",$sel[0]->stt_value);
            return $item[1];
        }
    }

    public function bookSearch(){
    $sel = DB::connection('sqlsrv2')->select("select top 1 stt_id,stt_value from SysTempTab Order by stt_id desc");
    if (!empty($sel)) {
        $item = explode(",",$sel[0]->stt_value);
        if($item[0]==2) {
            $res = DB::table('tmxxb as a')
                ->leftJoin('wxxxb as b','b.控制号','=','a.控制号')
                ->where(['a.条形码'=>$item[1]])
                ->select('b.正题名 as name','b.责任者 as write','b.出版者 as public','b.索取号 as getCode','b.出版日期
                    as publicTime','b.页卷数 as pages')
                ->first();
            if (empty($res)) {
                echo "<html><body style='font-size: 30px;text-align: center'><a style=\"font-size: 20px;\" href='index'>信息错误，返回首页！</a></body></html>";
                die;
            }
            return view('home.bookSearch')->with('res',$res)->with('barCode',$item[1]);
        } else {
            $res = DB::table('dzxxb')
                ->where(['读者证号'=>$item[1]])
                ->select('读者姓名 as name','读者性别 as sex','读者单位 as from','读者类别 as type','借出册数
                    as amount','备注 as content')
                ->first();
            if (empty($res)) {
                echo "<html><body style='font-size: 30px;text-align: center'><a style=\"font-size: 20px;\" href='index'>信息错误，返回首页！</a></body></html>";
                die;
            }
            $borrows = DB::table('ltxxb as a')
                ->leftJoin('wxxxb as b','b.控制号','=','a.控制号')
                ->where(['a.读者证号'=>$item[1],'a.借还标志'=>'I'])
                ->select('a.条形码 as barcode','a.借出时刻 as borrowTime','b.正题名 as name','b.索取号 as getCode',
                    'a.应还时刻 as dueTime')
                ->get();
            if (empty($borrows)) {
                echo "<html><body style='font-size: 30px;text-align: center'><a style=\"font-size: 20px;\" href='index'>信息错误，返回首页！</a></body></html>";
                die;
            }
            $backs = DB::table('ltxxb as a')
                ->leftJoin('wxxxb as b','b.控制号','=','a.控制号  ')
                ->where(['a.读者证号'=>$item[1],'a.借还标志'=>'O'])
                ->select('a.条形码 as barcode','a.借出时刻 as borrowTime','b.正题名 as name','b.索取号 as getCode',
                    'a.应还时刻 as dueTime')
                ->get();
            if (empty($backs)) {
                echo "<html><body style='font-size: 30px;text-align: center'><a style=\"font-size: 20px;\" href='index'>信息错误，返回首页！</a></body></html>";
                die;
            }
            return view('home.readerSearch')
                ->with('res',$res)
                ->with('borrows',$borrows)->with('backs',$backs);
        }
    }
}

    //一周外借统计
    public function borrowAjax() {
        $time = date('Y-m-d',time());
        $time1 = date('Y-m-d',time()+3600*24);
        $time2 = date('Y-m-d',time()-3600*24);
        $time3 = date('Y-m-d',time()-2*3600*24);
        $time4 = date('Y-m-d',time()-3*3600*24);
        $time5 = date('Y-m-d',time()-4*3600*24);
        $time6 = date('Y-m-d',time()-5*3600*24);
        $time7 = date('Y-m-d',time()-6*3600*24);
        $arr[0] = DB::table('ltxxb') ->where('借出时刻', '>=', $time)->where('借出时刻', '<', $time1)->count();
        $arr[1] = DB::table('ltxxb') ->where('借出时刻', '>=', $time2)->where('借出时刻', '<', $time)->count();
        $arr[2] = DB::table('ltxxb') ->where('借出时刻', '>=', $time3)->where('借出时刻', '<', $time2)->count();
        $arr[3] = DB::table('ltxxb') ->where('借出时刻', '>=', $time4)->where('借出时刻', '<', $time3)->count();
        $arr[4] = DB::table('ltxxb') ->where('借出时刻', '>=', $time5)->where('借出时刻', '<', $time4)->count();
        $arr[5] = DB::table('ltxxb') ->where('借出时刻', '>=', $time6)->where('借出时刻', '<', $time5)->count();
        $arr[6] = DB::table('ltxxb') ->where('借出时刻', '>=', $time7)->where('借出时刻', '<', $time6)->count();
        return $arr;
    }

    //一周外借统计
    public function backAjax() {
        $time = date('Y-m-d',time());
        $time1 = date('Y-m-d',time()+3600*24);
        $time2 = date('Y-m-d',time()-3600*24);
        $time3 = date('Y-m-d',time()-2*3600*24);
        $time4 = date('Y-m-d',time()-3*3600*24);
        $time5 = date('Y-m-d',time()-4*3600*24);
        $time6 = date('Y-m-d',time()-5*3600*24);
        $time7 = date('Y-m-d',time()-6*3600*24);
        $arr[0] = DB::table('ltlsb') ->where('实还时刻', '>=', $time)->where('实还时刻', '<', $time1)->count();
        $arr[1] = DB::table('ltlsb') ->where('实还时刻', '>=', $time2)->where('实还时刻', '<', $time)->count();
        $arr[2] = DB::table('ltlsb') ->where('实还时刻', '>=', $time3)->where('实还时刻', '<', $time2)->count();
        $arr[3] = DB::table('ltlsb') ->where('实还时刻', '>=', $time4)->where('实还时刻', '<', $time3)->count();
        $arr[4] = DB::table('ltlsb') ->where('实还时刻', '>=', $time5)->where('实还时刻', '<', $time4)->count();
        $arr[5] = DB::table('ltlsb') ->where('实还时刻', '>=', $time6)->where('实还时刻', '<', $time5)->count();
        $arr[6] = DB::table('ltlsb') ->where('实还时刻', '>=', $time7)->where('实还时刻', '<', $time6)->count();
        return $arr;
    }

    //一年外借统计
    public function yearBorrowAjax() {
        $time = '2017-07-01';
        $time1 = '2017-06-01';
        $time2 = '2017-05-01';
        $time3 = '2017-04-01';
        $time4 = '2017-03-01';
        $time5 = '2017-02-01';
        $time6 = '2017-01-01';
        $time7 = '2017-08-01';
        $arr[0] = DB::table('ltxxb') ->where('借出时刻', '>=', $time)->where('借出时刻', '<', $time7)->count();
        $arr[1] = DB::table('ltxxb') ->where('借出时刻', '>=', $time1)->where('借出时刻', '<', $time)->count();
        $arr[2] = DB::table('ltxxb') ->where('借出时刻', '>=', $time2)->where('借出时刻', '<', $time1)->count();
        $arr[3] = DB::table('ltxxb') ->where('借出时刻', '>=', $time3)->where('借出时刻', '<', $time2)->count();
        $arr[4] = DB::table('ltxxb') ->where('借出时刻', '>=', $time4)->where('借出时刻', '<', $time3)->count();
        $arr[5] = DB::table('ltxxb') ->where('借出时刻', '>=', $time5)->where('借出时刻', '<', $time4)->count();
        $arr[6] = DB::table('ltxxb') ->where('借出时刻', '>=', $time6)->where('借出时刻', '<', $time5)->count();
        return $arr;
    }

    //微信图书馆
    public function wechatBook() {
        $res = DB::connection('sqlsrv2')->table('BookInfo_RFIDLibrary_SenseitDB as a')
            ->leftJoin('BookInformationTable_checkmachinesystem_SenseitDB as b','b.frid_id','=','a.RFID_ID')
            ->select('b.bookbarcode','a.MacName','b.name')
            ->get();
        if (empty($res)) {
            $res = [];
        }
        return view('home.wechatBook')->with('res',$res);
    }

    //手机微型图书馆
    public function miniBook() {
        $res = DB::connection('sqlsrv2')->table('BookInfo_RFIDLibrary_SenseitDB as a')
            ->leftJoin('BookInformationTable_checkmachinesystem_SenseitDB as b','b.frid_id','=','a.RFID_ID')
            ->select('b.bookbarcode','a.MacName','b.name')
            ->get();
        if (empty($res)) {
            $res = [];
        }
        return view('home.miniBook')->with('res',$res);
    }

    //导航
    public function isWhere() {
        return view('home.isWhere');
    }
}
